<?php

class Default_Model_Upgrade_StudSemeTalk extends Default_Model_Upgrade_Core
{
    protected $_table = 'stud_seme_talk';

    protected $_targetTable = array('student_interview');

    public function upgrade()
    {
        if ($this->isUpgrade())
        return 'isUpgrde';
        else {
            $hasData = 0 ;
            // 先判斷是否有資料
            $query = "SELECT COUNT(*)  AS cc FROM student_guidance";
            $res = $this->_newdbPdo->query($query)->fetch();

            $hasData = $res['cc'];
            // drop FOREIGN KEY
            $query = "ALTER TABLE `student_interview` DROP FOREIGN KEY `student_interview_ibfk_1`";
            $this->_newdbPdo->exec($query) ;
            $query = "ALTER TABLE `student_interview`  ENGINE = MYISAM";
            $this->_newdbPdo->exec($query);

            $query = "SELECT a.*,b.student_sn  FROM stud_seme_talk a ,stud_base b WHERE a.stud_id=b.stud_id";
            $res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
            foreach($res as $row) {
                $arr = array();
                $year=  (int)substr($row['seme_year_seme'],0,-1);
                $semester = (int)substr($row['seme_year_seme'],-1);
                $arr['student_sn'] = $row['student_sn'];
                $arr['year'] = $year;
                $arr['semester'] = $semester;
                if ($hasData) {
                    $find = $this->_newdbPdo->query("SELECT student_sn FROM student_interview
				  WHERE  student_sn ='{$arr['student_sn']}'
				  AND year ='{$arr['year']}'
				  AND semester ='{$arr['semester']}'
				  ");
                    if ($find->rowCount() >0)
                    continue;
                }

                $arr['date'] = $row['sst_date'];
                $arr['connecter'] = $this->iconv($row['sst_name']);
                $arr['topic'] = $this->iconv($row['sst_main']);
                $arr['content'] = $this->iconv($row['sst_memo']);
                $arr['teacher_sn'] = $row['teach_id'];
                $arr['update_time'] = $row['update_time'];
                $this->fromArray('student_interview',$arr);
            }
            // 加入 FOREIGN KEY
            $query = "ALTER TABLE `student_interview`  ENGINE = INNODB";
            $this->_newdbPdo->exec($query);
            $query = "ALTER TABLE `student_interview`
  ADD CONSTRAINT `student_interview_ibfk_1` FOREIGN (`student_sn`) REFERENCES `student` (`sn`) ON DELETE NO ACTION ON UPDATE CASCADE;";
            $this->_newdbPdo->exec($query);

            $log['memo'] = 'stud_seme_talk  升級';
            $log['upgrade_time']	= date("Y-m-d h:i:s");

            $this->finishUpgrade($log);
        }
    }

}